import java.awt.Point;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.sql.Date;
import java.util.ArrayList;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.CreationHelper;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class ExcelManager {

	public Workbook openReadExcelFile(String FileName) {

		Workbook wb = null;
		try {
			wb = new XSSFWorkbook(new FileInputStream(FileName));
			
		} catch (FileNotFoundException e) {
			// TODO Auto-generated catch block
			System.out.println("Filenot Found exception");
			e.printStackTrace();
		} catch (IOException e) {
			// TODO Auto-generated catch block
			System.out.println("read excel file err");
			e.printStackTrace();
		}
		return wb;
	}

	public Sheet getReadWorksheet(Workbook workbook, int SheetNum) {
		Sheet sheet = workbook.getSheetAt(SheetNum);
		return sheet;
	}

	public ArrayList<Object[]> readDataOnSheet(Sheet sheet, Point src, Point des) {
		ArrayList<Object[]> list = new ArrayList<Object[]>();
		Row row;
		Cell cell;
		CellStyle cellstyle = sheet.getWorkbook().createCellStyle();
		CreationHelper helper = sheet.getWorkbook().getCreationHelper();
		cellstyle.setDataFormat(helper.createDataFormat().getFormat("yyyy-m-d h:mm"));
		for (int j = (int) src.getY(); j <= des.getY(); j++) {
			Object[] data = new Object[(int) (des.getY() - src.getY()) + 1];
			row = sheet.getRow(j);

			for (int i = (int) src.getX(); i <= des.getX(); i++) {
				cell = row.getCell(i);
				CellStyle c;

				if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {

					if (DateUtil.isCellDateFormatted(cell)) { // in case of DATE
																// format
						data[(int) (i - src.getX())] = cell.getDateCellValue();
					} else {
						data[(int) (i - src.getX())] = cell
								.getNumericCellValue();
					}

				} else if (cell.getCellType() == Cell.CELL_TYPE_STRING) {
					data[(int) (i - src.getX())] = cell.getStringCellValue();
				} else if (cell.getCellType() == Cell.CELL_TYPE_BOOLEAN) {
					data[(int) (i - src.getX())] = cell.getBooleanCellValue();
				} else if (cell.getCellType() == Cell.CELL_TYPE_FORMULA) {
					data[(int) (i - src.getX())] = cell.getCellFormula();
				} else
					;
			}
			list.add(data);
		}
		return list;
	}
	public boolean createExcelFile(Workbook wb, String Filename)
	{
		FileOutputStream fileOut = null;
		try {
			fileOut = new FileOutputStream(Filename);
			wb.write(fileOut);
		} catch (FileNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (IOException e){
			 System.out.println("creating excel file err");
		} finally{
			try {
				fileOut.close();
				return false;
			} catch (IOException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return true;
	}
	public Workbook createWritableWorksheet() {
		Workbook workbook = new XSSFWorkbook();
		return workbook;
	}

	public Sheet getWritableWorksheet(Workbook workbook,
			String SheetName) {
		return workbook.createSheet(SheetName);
	}

	public boolean WriteDataOnSheet(Sheet sheet, Point begin,
			ArrayList<Object[]> Data) {
		Object[] data;
		Row row;
		Cell cell;
		for (int i = (int) begin.getY(); i < Data.size(); i++) {
			data = Data.get(i);
			row = sheet.createRow(i);
			for (int j = 1; j < Data.get(i).length; j++) {
				cell = row.createCell(j);
				try {
					if (data[j] instanceof String) {
						cell.setCellValue((String) data[j]);
					} else if (data[j] instanceof Date) {
						cell.setCellValue((Date)data[j]);
					} else if (data[j] instanceof Integer || data[j] instanceof Float
							|| data[j] instanceof Double) {
						cell.setCellValue((double)data[j]);
					} else if (data[j] instanceof Boolean){
						cell.setCellValue((Boolean)data[j]);
					} else ;
				} catch (Exception e) {
					System.out.println("excel write err");
				}
			}
		}
		return true;
	}
}
